-- exec sp_fetch_lot_info_by_lot_id 6621
-- Lot_number, Vendor_lot_Number, Comments, Date Code, Hold
-- select * from lot_table
/*
SELECT  MAX(Timestamp) FROM SORT_LOT_HISTORY WHERE Lot_ID = 8171 AND SL_Die_Qty_Out <> 0 AND SL_Die_Qty_Out IS NOT NULL
select * from Sort_lot_History where lot_id = 8171
select Lot_Number, Lot_Current_Status, FL_Input_Quantity, FL_Output_Quantity, 
convert(varchar,FL_Date_In,101) FL_Date_In, convert(varchar,FL_Date_Out,101) FL_Date_Out, 
FL_Yield, PurchaseOrder_ID, isnull(convert(varchar, FL_Die_Size), '') FL_Die_Size, 
FL_Gross_Die_Wafer, Lot_Date_Code, FL_Vendor_Lot_Number, Lot_Comments, Is_Hold from Lot_Table, 
Fab_Lot where Lot_Table.Lot_id = Fab_Lot.Lot_id and Lot_Table.Lot_id = ?
*/


create PROC sp_fetch_lot_info_by_lot_id @lotId INT
AS
BEGIN
	DECLARE @results TABLE
		( qtyIn INT, dateIn datetime, status VARCHAR(50), qtyOut INT, dateOut datetime,
		  Lot_number VARCHAR(50), Date_Code VARCHAR(50), Vendor_Lot_Number VARCHAR(1000), 
		  Comments VARCHAR(1000), Hold BIT )

	DECLARE @processStepId INT
	DECLARE @qtyIn INT
	DECLARE @qtyOut INT
	DECLARE @processType VARCHAR(50)
	DECLARE @dateIn VARCHAR(30)
	DECLARE @dateOut VARCHAR(30)
	DECLARE @status VARCHAR(50)
	DECLARE @lotNumber VARCHAR(50)
	DECLARE @dateCode VARCHAR(50)
	DECLARE @vendorLotNumber VARCHAR(1000)
	DECLARE @comments VARCHAR(1000)
	DECLARE @hold BIT
	DECLARE @max_timestamp DATETIME

	select @STATUS = LOT_CURRENT_STATUS from lot_table where lot_id = @LOTID

	IF @status <> 'Close'
	BEGIN
		SELECT  @LOTNUMBER = LOT_NUMBER,@VENDORLOTNUMBER = VENDOR_LOT_NUMBER, @PROCESSTYPE = PS_TYPE_NAME, 
			@QTYIN = (CASE WHEN ( PS_TYPE_NAME = 'FAB' OR PS_TYPE_NAME = 'WAFER STORE' OR PS_TYPE_NAME = 'BUMP') THEN WAFER_QTY_IN
				 ELSE QTY_IN END),
			@DATEIN = DATE_IN,
			@QTYOUT = (CASE WHEN ( PS_TYPE_NAME = 'FAB' OR PS_TYPE_NAME = 'WAFER STORE' OR PS_TYPE_NAME = 'BUMP') THEN WAFER_QTY_OUT
				 ELSE QTY_OUT END),
			@DATEOUT = DATE_OUT,  @DATECODE = LOT_DATE_CODE, 
			@COMMENTS = CONVERT(VARCHAR(1000), LOT_COMMENTS ), @HOLD = LOT_MANUAL_HOLD 
		FROM 	
			LOT_TABLE LT, PROCESS_STEP PS 
		WHERE 
			LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LOT_ID = @LOTID 
	END
	ELSE
	BEGIN
		select @max_timestamp = MAX(Timestamp) FROM lot_table_history where lot_id = @lotid and ( WAFER_QTY_IN > 0 or QTY_IN > 0 )

		SELECT  @LOTNUMBER = LOT_NUMBER,@VENDORLOTNUMBER = VENDOR_LOT_NUMBER, @PROCESSTYPE = PS_TYPE_NAME, 
			@QTYIN = (CASE WHEN ( PS_TYPE_NAME = 'FAB' OR PS_TYPE_NAME = 'WAFER STORE' OR PS_TYPE_NAME = 'BUMP') THEN WAFER_QTY_IN
				 ELSE QTY_IN END),
			@DATEIN = DATE_IN,
			@QTYOUT = (CASE WHEN ( PS_TYPE_NAME = 'FAB' OR PS_TYPE_NAME = 'WAFER STORE' OR PS_TYPE_NAME = 'BUMP') THEN WAFER_QTY_OUT
				 ELSE QTY_OUT END),
			@DATEOUT = DATE_OUT,  @DATECODE = LOT_DATE_CODE, 
			@COMMENTS = CONVERT(VARCHAR(1000), LOT_COMMENTS ), @HOLD = LOT_MANUAL_HOLD 
		FROM 	
			LOT_TABLE_HISTORY LT, PROCESS_STEP PS 
		WHERE 
			LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LOT_ID = @LOTID  and lt.timestamp = @max_timestamp
	END
	
	if @qtyIn > 0
	begin
		INSERT INTO @results ( qtyIn, dateIn, status, qtyOut, dateOut, Lot_number, Date_Code, Comments, Hold, Vendor_Lot_Number ) VALUES( @qtyIn, @dateIn, @status, @qtyOut, @dateOut, @lotNumber, @dateCode, @comments, @hold, @vendorLotNumber )
	end

 	SELECT qtyIn, dateIn, status, qtyOut, dateOut, Lot_number, Date_Code, Comments, Hold, Vendor_Lot_Number FROM @results
END
